import json
import pandas as pd
from datetime import datetime, timedelta
import match

import logging

from utils.verifyParameters import verify

# 配置日志
logging.basicConfig(level=logging.INFO)

# 尝试逐个格式解析日期
def clean_and_parse_dates(date_series):
    # 先统一替换中文上午/下午
    cleaned = date_series.astype(str).str.strip() \
        .str.replace('上午', 'AM', regex=False) \
        .str.replace('下午', 'PM', regex=False)

    # 定义多个格式逐个尝试
    formats = [
        "%Y-%m-%d %p %I:%M:%S",  # 2025-5-26 上午 09:30:45
        "%Y/%m/%d %H:%M:%S",     # 2025/5/27 8:47:17
        "%Y-%m-%d %H:%M:%S",     # 标准格式
        "%Y/%m/%d %p %I:%M:%S",  # 2025/5/27 上午 08:47:17
    ]

    parsed_dates = pd.Series(index=cleaned.index, dtype='datetime64[ns]')
    remaining = cleaned[pd.isna(parsed_dates)].copy()

    for fmt in formats:
        if not remaining.empty:
            temp = pd.to_datetime(remaining, format=fmt, errors='coerce')
            parsed_dates.update(temp)
            remaining = cleaned[pd.isna(parsed_dates)]

    return parsed_dates

def process_xiayi_reconciliation(recon_start_time, recon_end_time, file_path, fault_tolerant, station_id, ignore_time):
    """
    处理夏邑对账单的特殊逻辑

    Args:
        recon_start_time (str): 对账开始时间，格式为 "YYYY-MM-DD HH:MM:SS"
        recon_end_time (str): 对账结束时间，格式为 "YYYY-MM-DD HH:MM:SS"
        file_path (str): 上传的Excel文件路径

    Returns:
        dict: 处理结果的JSON数据
    """
    try:
        # region 配置
        station_config = {
            "name": "夏邑",
            "ids": [46, 47],
            "nms": ["(.*)南区(.*)", "(.*)北区(.*)"],
            "main_body_gas_station": 11,
            "file_keyword": "(.*)夏邑(.*)",
            "columns": {
                "create_time": "日期",
                "car_number": "车号",
                "gas_num": "加气量",
                "gas_price": "单价",
            },
            "diff_num": 1,  # 差异在x公斤以内的设置为疑似匹配
        }

        verify(fault_tolerant, station_id, ignore_time, station_config)

        # 处理时间范围
        start_time = datetime.strptime(recon_start_time, "%Y-%m-%d %H:%M:%S")
        start_time = (start_time - timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")
        end_time = datetime.strptime(recon_end_time, "%Y-%m-%d %H:%M:%S")
        end_time = (end_time + timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")

        # step 1 获取Excel中的可用sheet
        closest_sheet = None
        min_diff = None
        closest_sheet_name = None
        # 加载 Excel 文件，获取所有工作表
        excel_file = pd.ExcelFile(file_path)
        # 获取所有的sheet
        sheet_names = excel_file.sheet_names
        # 循环每个工作表
        for sheet in sheet_names:
            header_row = None
            df_tmp = pd.read_excel(file_path, sheet_name=sheet)
            if all(item in df_tmp.columns.tolist() for item in list(station_config["columns"].values())):
                header_row = -1
            else:
                for row in df_tmp.head(2).itertuples():
                    if all(item in list(row[1:]) for item in list(station_config["columns"].values())):
                        header_row = row.Index
                        break
            if header_row is not None:
                df = pd.read_excel(file_path, sheet_name=sheet, skiprows=header_row + 1)
                try:
                    # 使用自定义函数解析日期
                    df[station_config["columns"]["create_time"]] = clean_and_parse_dates(
                        df[station_config["columns"]["create_time"]],
                    )
                    df = df.dropna(subset=[station_config["columns"]["create_time"]])
                    df = df.dropna(subset=[station_config["columns"]["gas_num"]])
                    latest_time = df[station_config["columns"]["create_time"]].max()
                    if pd.notnull(latest_time):
                        # 计算与当天的差值
                        time_diff = abs(
                            (latest_time.date() - datetime.strptime(recon_end_time, "%Y-%m-%d %H:%M:%S").date()).days
                        )
                        # 更新最接近的sheet
                        if min_diff is None or time_diff < min_diff:
                            min_diff = time_diff
                            closest_sheet = df
                            closest_sheet_name = sheet
                except Exception as e:
                    logging.error(f"处理{sheet}时出错: {e}")

        try:
            # 夏邑特殊处理。
            # 需要对选中的stationdata进行左右合并操作
            df_special = pd.read_excel(file_path, sheet_name=closest_sheet_name)
            # 分割东区和西区数据
            # 东区数据：前10列（索引 0 到 9）
            east_columns = df_special.columns[:10]
            df_east = df_special[east_columns].copy()
            df_east.columns = df_east.iloc[0]
            df_east = df_east.iloc[1:].copy()  # 删除前两行，从第三行开始保留
            df_east = df_east[list(station_config["columns"].values())]
            df_east["站点"] = "南区"

            # 西区数据：从第11列开始（索引 9 到 17）
            west_columns = df_special.columns[10:]
            df_west = df_special[west_columns].copy()
            df_west.columns = df_west.iloc[0]
            df_west = df_west.iloc[1:].copy()  # 删除前两行，从第三行开始保留
            df_west = df_west[list(station_config["columns"].values())]
            df_west["站点"] = "北区"
            df_east = df_east.dropna().copy()
            df_west = df_west.dropna().copy()

            # 合并
            station_data = pd.concat([df_east, df_west], axis=0, ignore_index=True)
            station_config["columns"]["station_name"] = "站点"
            station_config["nms"] = ["(.*)南区(.*)", "(.*)北区(.*)"]
            station_data[station_config["columns"]["create_time"]] = clean_and_parse_dates(
                station_data[station_config["columns"]["create_time"]],
            )
            station_data = station_data.dropna(subset=[station_config["columns"]["create_time"]]).copy()
            station_data = match.set_station_id_column(station_data, station_config)

        except Exception as e:
            logging.error('配置文件与excel不一致')
            raise RuntimeError('配置文件与excel不一致') from e
        logging.info(f"{len(station_data)} {closest_sheet_name}")

        station_dfs_dict = {}

        # step 2 处理Excel 如果是多个气站并且Excel中的字段配置包含station_name，就需要分多个气站进行对比。
        if "station_name" in station_config["columns"] and len(station_config["ids"]) > 1 and "nms" in station_config:
            station_names = station_config["nms"]
            station_name_col = station_config["columns"]["station_name"]
            for idx, pattern in enumerate(station_names):
                matched_df = station_data[station_data[station_name_col].astype(str).str.match(pattern, na=False)]
                # 如果有详细时间，需要对excel数据按照详细时间进行排序
                non_midnight_count = (
                        matched_df[station_config["columns"]["create_time"]].dt.strftime("%H:%M:%S") != "00:00:00"
                ).sum()
                if len(matched_df) > 0 and (non_midnight_count / len(matched_df)) > 0.5:
                    # 超过50%即为"绝大多数"
                    matched_df = matched_df.sort_values(by=station_config["columns"]["create_time"])
                station_dfs_dict[station_config["ids"][idx]] = matched_df

        match_result = []

        # step 3 如果有station_dfs_dict就循环这个，然后每个站点单独获取数据并对比，否则就获取配置中的所有站点数据并进行对比
        if len(station_dfs_dict) > 0:
            for i in station_dfs_dict:
                online_data = match.get_online_data([i], start_time, end_time)
                # 开始对比
                match_result.append(match.match_data_v1(station_dfs_dict[i], online_data, station_config))
        else:
            # excel获取时间区间内的数据
            if station_config.get("excel_get_time_range", False):
                station_data = station_data[
                    station_data[station_config["columns"]["create_time"]].between(start_time, end_time)]
            online_data = match.get_online_data(station_config['ids'], start_time, end_time)
            # 开始对比
            match_result.append(match.match_data_v1(station_data, online_data, station_config))

        # 生成JSON结果
        json_result = match.create_json_result_v2(match_result, station_config, start_time, end_time)
        return json_result

    except Exception as e:
        raise Exception(f"处理夏邑对账单时出错: {str(e)}")
